HCL Notes and Domino Application Development wiki: Developing Form- and View-based Applications: split checkbox field into multiple fields without re-saving all existing docs

split checkbox field into multiple fields without re-saving all existing docs

No abstract provided.

Existing field is a multi-value check box. Let's call this field cities
Need to split into multiple fields without re-saving every document in the database. Let's call these fields cities_MN and cities_WI
Step 1: Hide cities field
Step 2: New fields cities_MN & cities_WI are editable checkbox fields with default value of cities

This is the concept. Even before a document is re-saved this change will allow the appropriate value(s) from cities field to display in cities_MN & cities_WI fields
How do they separate? Based on the field choices of the new fields cities_MN & cities_WI

Pitfalls. Prior to re-saving, data in existing docs is saved in cities field but cities_MN & cities_WI fields are still null "". Need to be careful with any view selection criteria, agents, or any other processing we do
Example:

cities field has field choices of "Appleton, WI"; "Madison, WI"; "Woodbury, MN"; "Saint Paul, MN"; "New York, NY" and let's just say all 5 are checked, so cities field has all 5 values stored
Pre-save & after the split

cities_MN field has field choices "Woodbury, MN"; "Saint Paul, MN"; "Minneapolis, MN"
cities_MN field has value stored as "", but when displayed will display "Woodbury, MN" & "Saint Paul, MN" (because the default value on this field is "cities" and Notes can display this even before it's saved and updated)
"Minneapolis, MN" will not be displayed since it wasn't checked in the cities field

Views and how to handle:

cities will always be in available in views, but won't be correct once a doc is saved and cities_MN & cities_WI are created, then we MUST use the new cities_MN & cities_WI fields to ensure data accuracy
again, our cities field has field choices of "Appleton, WI"; "Madison, WI"; "Woodbury, MN"; "Saint Paul, MN"; "New York, NY", and assuming we don't want do display cities outside WI/MN, our view selection formula could look something like this:

include := "";
@If(@IsAvailable(cities_MN);
values := cities_MN:cities_WI;
values := cities;
@If(@Contains(values; "Appleton, WI"); (include := "yes"); "");
@If(@Contains(values; "Madison, WI"); (include := "yes"); "");
@If(@Contains(values; "Saint Paul, MN"); (include := "yes"); "");
@If(@Contains(values; "Woodbury, MN"); (include := "yes"); "");
SELECT
(include = "yes")

@IsAvailable checks to see if the field exists, and we could have picked either cities_MN or cities_WI since they're both created at the exact same time
this view is going to display all the cities that are checked on the form

when the new fields aren't there we go ahead and display the old cities field
when the new fields exist (though they could still be null "") we display cities_MN and cities_WI

pitfall: cities is hidden and contains "bad data" once the new fields are created, and this is a drawback of splitting the field and is something we need to handle in views and other situations where we handle data

Conclusion:

It would have been better to find a way not to split the field like this in the first place, but it was already done before I got involved. Next time, I'd probably start the same way. Hide the old field and split into 2 new fields defaulted to the old field. Then I'd write an agent to re-compute & re-save all docs to force the new fields to pickup and save their defaults. Then I'd get rid of the original field. What seems like a good idea sometimes turns into a mess.